package com.luo.backend.repository;

import com.luo.backend.entity.Review;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

/**
 * 评价数据访问接口
 */
@Repository
public interface ReviewRepository extends JpaRepository<Review, Long> {
    
    /**
     * 根据订单ID查找评价（只查询正常状态的）
     */
    Optional<Review> findByOrderIdAndStatus(Long orderId, Integer status);
    
    /**
     * 检查订单是否已评价
     */
    boolean existsByOrderId(Long orderId);
    
    /**
     * 根据用户ID分页查询评价（只查询正常状态的）
     */
    Page<Review> findByUserIdAndStatusOrderByCreatedAtDesc(Long userId, Integer status, Pageable pageable);
    
    /**
     * 根据商家ID查询评价列表（不分页，只查询正常状态的）
     */
    List<Review> findByMerchantIdAndStatus(Long merchantId, Integer status);
    
    /**
     * 根据商家ID分页查询评价（只查询正常状态的）
     */
    Page<Review> findByMerchantIdAndStatusOrderByCreatedAtDesc(Long merchantId, Integer status, Pageable pageable);
    
    /**
     * 根据商家ID和评分筛选评价（只查询正常状态的）
     */
    Page<Review> findByMerchantIdAndOverallRatingAndStatusOrderByCreatedAtDesc(Long merchantId, Integer rating, Integer status, Pageable pageable);
    
    /**
     * 根据商家ID和是否有回复筛选评价（只查询正常状态的）
     */
    @Query("SELECT r FROM Review r WHERE r.merchantId = :merchantId AND r.status = :status AND " +
           "(:hasReply = true AND r.merchantReply IS NOT NULL OR :hasReply = false AND r.merchantReply IS NULL) " +
           "ORDER BY r.createdAt DESC")
    Page<Review> findByMerchantIdAndHasReply(@Param("merchantId") Long merchantId, 
                                              @Param("hasReply") Boolean hasReply,
                                              @Param("status") Integer status,
                                              Pageable pageable);
    
    /**
     * 根据商家ID、评分和是否有回复筛选评价（只查询正常状态的）
     */
    @Query("SELECT r FROM Review r WHERE r.merchantId = :merchantId AND r.overallRating = :rating AND r.status = :status AND " +
           "(:hasReply = true AND r.merchantReply IS NOT NULL OR :hasReply = false AND r.merchantReply IS NULL) " +
           "ORDER BY r.createdAt DESC")
    Page<Review> findByMerchantIdAndRatingAndHasReply(@Param("merchantId") Long merchantId, 
                                                        @Param("rating") Integer rating,
                                                        @Param("hasReply") Boolean hasReply,
                                                        @Param("status") Integer status,
                                                        Pageable pageable);
    
    /**
     * 根据商家ID和是否有图片筛选评价（只查询正常状态的）
     */
    @Query("SELECT r FROM Review r WHERE r.merchantId = :merchantId AND r.status = :status AND " +
           "(:hasImage = true AND r.imagesJson IS NOT NULL AND r.imagesJson != '' OR :hasImage = false) " +
           "ORDER BY r.createdAt DESC")
    Page<Review> findByMerchantIdAndHasImage(@Param("merchantId") Long merchantId,
                                              @Param("hasImage") Boolean hasImage,
                                              @Param("status") Integer status,
                                              Pageable pageable);
    
    /**
     * 根据商家ID、评分和是否有图片筛选评价（只查询正常状态的）
     */
    @Query("SELECT r FROM Review r WHERE r.merchantId = :merchantId AND r.overallRating = :rating AND r.status = :status AND " +
           "(:hasImage = true AND r.imagesJson IS NOT NULL AND r.imagesJson != '' OR :hasImage = false) " +
           "ORDER BY r.createdAt DESC")
    Page<Review> findByMerchantIdAndRatingAndHasImage(@Param("merchantId") Long merchantId,
                                                       @Param("rating") Integer rating,
                                                       @Param("hasImage") Boolean hasImage,
                                                       @Param("status") Integer status,
                                                       Pageable pageable);
    
    /**
     * 统计商家的总评价数（只统计正常状态的）
     */
    long countByMerchantIdAndStatus(Long merchantId, Integer status);
    
    /**
     * 统计商家的平均评分（只统计正常状态的）
     */
    @Query("SELECT AVG(r.overallRating) FROM Review r WHERE r.merchantId = :merchantId AND r.status = :status")
    Double getAverageRatingByMerchantId(@Param("merchantId") Long merchantId, @Param("status") Integer status);
    
    /**
     * 统计商家各评分的数量（只统计正常状态的）
     */
    @Query("SELECT r.overallRating, COUNT(r) FROM Review r WHERE r.merchantId = :merchantId AND r.status = :status GROUP BY r.overallRating")
    List<Object[]> getRatingDistributionByMerchantId(@Param("merchantId") Long merchantId, @Param("status") Integer status);
    
    /**
     * 统计商家待回复的评价数（只统计正常状态的）
     */
    @Query("SELECT COUNT(r) FROM Review r WHERE r.merchantId = :merchantId AND r.merchantReply IS NULL AND r.status = :status")
    long countPendingRepliesByMerchantId(@Param("merchantId") Long merchantId, @Param("status") Integer status);
    
    /**
     * 根据商家ID、评分、是否有回复和是否有图片筛选评价（只查询正常状态的）
     */
    @Query("SELECT r FROM Review r WHERE r.merchantId = :merchantId AND r.overallRating = :rating AND r.status = :status AND " +
           "(:hasReply = true AND r.merchantReply IS NOT NULL OR :hasReply = false AND r.merchantReply IS NULL) AND " +
           "(:hasImage = true AND r.imagesJson IS NOT NULL AND r.imagesJson != '' OR :hasImage = false) " +
           "ORDER BY r.createdAt DESC")
    Page<Review> findByMerchantIdAndRatingAndHasReplyAndHasImage(@Param("merchantId") Long merchantId,
                                                                  @Param("rating") Integer rating,
                                                                  @Param("hasReply") Boolean hasReply,
                                                                  @Param("hasImage") Boolean hasImage,
                                                                  @Param("status") Integer status,
                                                                  Pageable pageable);
    
    /**
     * 根据商家ID、是否有回复和是否有图片筛选评价（只查询正常状态的）
     */
    @Query("SELECT r FROM Review r WHERE r.merchantId = :merchantId AND r.status = :status AND " +
           "(:hasReply = true AND r.merchantReply IS NOT NULL OR :hasReply = false AND r.merchantReply IS NULL) AND " +
           "(:hasImage = true AND r.imagesJson IS NOT NULL AND r.imagesJson != '' OR :hasImage = false) " +
           "ORDER BY r.createdAt DESC")
    Page<Review> findByMerchantIdAndHasReplyAndHasImage(@Param("merchantId") Long merchantId,
                                                         @Param("hasReply") Boolean hasReply,
                                                         @Param("hasImage") Boolean hasImage,
                                                         @Param("status") Integer status,
                                                         Pageable pageable);
    
    /**
     * 根据商家ID、评分和是否有回复和是否有图片筛选评价（只查询正常状态的）
     */
    @Query("SELECT r FROM Review r WHERE r.merchantId = :merchantId AND r.overallRating = :rating AND r.status = :status AND " +
           "(:hasImage = true AND r.imagesJson IS NOT NULL AND r.imagesJson != '' OR :hasImage = false) AND " +
           "(:hasReply = true AND r.merchantReply IS NOT NULL OR :hasReply = false AND r.merchantReply IS NULL) " +
           "ORDER BY r.createdAt DESC")
    Page<Review> findByMerchantIdAndRatingAndHasImageAndHasReply(@Param("merchantId") Long merchantId,
                                                                  @Param("rating") Integer rating,
                                                                  @Param("hasImage") Boolean hasImage,
                                                                  @Param("hasReply") Boolean hasReply,
                                                                  @Param("status") Integer status,
                                                                  Pageable pageable);
    
    /**
     * 计算商品平均评分（只统计正常状态的）
     */
    @Query("SELECT AVG(r.overallRating) FROM Review r WHERE r.foodId = :foodId AND r.status = :status")
    Double getAverageRatingByFoodId(@Param("foodId") Long foodId, @Param("status") Integer status);
    
    /**
     * 统计商品评价数量（只统计正常状态的）
     */
    Long countByFoodIdAndStatus(Long foodId, Integer status);
}




